package cn.edu.baiyunu.myapplication.data.dao

import androidx.room.Dao
import androidx.room.Delete
import androidx.room.Insert
import androidx.room.OnConflictStrategy
import androidx.room.Query
import androidx.room.Update
import cn.edu.baiyunu.myapplication.data.model.Transaction
import kotlinx.coroutines.flow.Flow

/**
 * 交易记录数据访问对象
 */
@Dao
interface TransactionDao {
    /**
     * 获取所有交易记录，按日期降序排列
     */
    @Query("SELECT * FROM transactions ORDER BY date DESC")
    fun getAllTransactions(): Flow<List<Transaction>>
    
    /**
     * 根据ID获取单条交易记录
     */
    @Query("SELECT * FROM transactions WHERE id = :id")
    suspend fun getTransactionById(id: String): Transaction?
    
    /**
     * 获取指定日期范围内的交易记录
     */
    @Query("SELECT * FROM transactions WHERE date BETWEEN :startDate AND :endDate ORDER BY date DESC")
    fun getTransactionsByDateRange(startDate: Long, endDate: Long): Flow<List<Transaction>>
    
    /**
     * 获取指定月份的交易记录
     */
    @Query("SELECT * FROM transactions WHERE strftime('%Y-%m', datetime(date/1000, 'unixepoch')) = :yearMonth ORDER BY date DESC")
    fun getTransactionsByYearMonth(yearMonth: String): Flow<List<Transaction>>
    
    /**
     * 获取最近的n条交易记录
     */
    @Query("SELECT * FROM transactions ORDER BY date DESC LIMIT :limit")
    fun getRecentTransactions(limit: Int): Flow<List<Transaction>>
    
    /**
     * 获取指定月份的收入总额
     */
    @Query("SELECT COALESCE(SUM(amount), 0) FROM transactions WHERE type = 'income' AND strftime('%Y-%m', datetime(date/1000, 'unixepoch')) = :yearMonth")
    fun getTotalIncomeByYearMonth(yearMonth: String): Flow<Double>
    
    /**
     * 获取指定月份的支出总额
     */
    @Query("SELECT COALESCE(SUM(amount), 0) FROM transactions WHERE type = 'expense' AND strftime('%Y-%m', datetime(date/1000, 'unixepoch')) = :yearMonth")
    fun getTotalExpenseByYearMonth(yearMonth: String): Flow<Double>
    
    /**
     * 插入或替换交易记录
     */
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertTransaction(transaction: Transaction)
    
    /**
     * 更新交易记录
     */
    @Update
    suspend fun updateTransaction(transaction: Transaction)
    
    /**
     * 删除交易记录
     */
    @Delete
    suspend fun deleteTransaction(transaction: Transaction)
    
    /**
     * 根据ID删除交易记录
     */
    @Query("DELETE FROM transactions WHERE id = :id")
    suspend fun deleteTransactionById(id: String)
} 